﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MVVM.Northwind.Employee.Utilities;

namespace MVVM.Northwind.Employee.DataAccess
{
    public class DatabaseGateway
    {
        private static DatabaseGateway _databaseGateway;
        private static Northwind_EmployeeDataContext db;
        private static object _syncroot = new object();
        private DatabaseGateway() { }

        public static DatabaseGateway CreateInstance()
        {
            try
            {
                lock (_syncroot)
                {
                    if (_databaseGateway == null)
                    {
                        _databaseGateway = new DatabaseGateway();
                    }
                    return _databaseGateway;
                }               
                
            }
            catch (Exception ex)
            {
                ErrorHandler.ErrorMessage = ex.ToString();
                return null;
            }
            
        }

        public List<Employee> GetEmployees()
        {
            try
            {
                List<Employee> employeeList = new List<Employee>();
                //Northwind_EmployeeDataContext db = new Northwind_EmployeeDataContext();
                db = new Northwind_EmployeeDataContext();
                var q = from e in db.Employees
                        select e;
                foreach (var item in q)
                {
                    Employee emp = new Employee();
                    emp.Address = item.Address;
                    emp.BirthDate = item.BirthDate;
                    emp.City = item.City;
                    emp.Country = item.Country;
                    emp.EmployeeID = item.EmployeeID;
                    emp.Extension = item.Extension;
                    emp.FirstName = item.FirstName;
                    emp.HireDate = item.HireDate;
                    emp.HomePhone = item.HomePhone;
                    emp.LastName = item.LastName;
                    emp.Notes = item.Notes;
                    emp.Photo = item.Photo;
                    emp.PostalCode = item.PostalCode;
                    emp.Region = item.Region;
                    emp.Title = item.Title;
                    employeeList.Add(emp);
                }
                return employeeList;
            }
            catch (Exception ex)
            {
                ErrorHandler.ErrorMessage = ex.ToString();
                return null;
            }
            
        }
        public List<Employee> SearchEmployees(string name)
        {
            try
            {
                List<Employee> employeeList = new List<Employee>();
                //Northwind_EmployeeDataContext db = new Northwind_EmployeeDataContext();
                db = new Northwind_EmployeeDataContext();
                var q = db.spSearchEmployeeByName(name);
                foreach (var item in q)
                {
                    Employee emp = new Employee();
                    emp.Address = item.Address;
                    emp.BirthDate = item.BirthDate;
                    emp.City = item.City;
                    emp.Country = item.Country;
                    emp.EmployeeID = item.EmployeeID;
                    emp.Extension = item.Extension;
                    emp.FirstName = item.FirstName;
                    emp.HireDate = item.HireDate;
                    emp.HomePhone = item.HomePhone;
                    emp.LastName = item.LastName;
                    emp.Notes = item.Notes;
                    emp.Photo = item.Photo;
                    emp.PostalCode = item.PostalCode;
                    emp.Region = item.Region;
                    emp.Title = item.Title;
                    employeeList.Add(emp);
                }
                return employeeList;
            }
            catch (Exception ex)
            {
                ErrorHandler.ErrorMessage = ex.Message;
                return null;
            }
        }

        public bool UpdateEmployee(Employee updatedEmp)
        {
            try
            {
                //Northwind_EmployeeDataContext db = new Northwind_EmployeeDataContext();
                int employeeID = updatedEmp.EmployeeID;
                db = new Northwind_EmployeeDataContext();
                Employee emp = new Employee();
                emp = db.Employees.Single(e =>  e.EmployeeID == employeeID);

                //update
                emp.Address = updatedEmp.Address;
                emp.BirthDate = updatedEmp.BirthDate;
                emp.City = updatedEmp.City;
                emp.Country = updatedEmp.Country;
                emp.EmployeeID = updatedEmp.EmployeeID;
                emp.Extension = updatedEmp.Extension;
                emp.FirstName = updatedEmp.FirstName;
                emp.HireDate = updatedEmp.HireDate;
                emp.HomePhone = updatedEmp.HomePhone;
                emp.LastName = updatedEmp.LastName;
                emp.Notes = updatedEmp.Notes;
                emp.Photo = updatedEmp.Photo;
                emp.PostalCode = updatedEmp.PostalCode;
                emp.Region = updatedEmp.Region;
                emp.Title = updatedEmp.Title;

                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                ErrorHandler.ErrorMessage = ex.Message;
                return false;
            }
        }
        public bool AddEmployee(Employee newEmployee)
        {
            try
            {
                //Northwind_EmployeeDataContext db = new Northwind_EmployeeDataContext();
                db.Employees.InsertOnSubmit(newEmployee);
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {

                ErrorHandler.ErrorMessage = ex.Message;
                return false;
            }
        }

        public bool DeleteEmployee(int employeeID)
        {
            try
            {
                //Northwind_EmployeeDataContext db = new Northwind_EmployeeDataContext();
                db = new Northwind_EmployeeDataContext();
                var emp = from e in db.Employees
                          where e.EmployeeID == employeeID
                          select e;                
                db.Employees.DeleteOnSubmit(emp as Employee);
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                ErrorHandler.ErrorMessage = ex.Message;
                return false;
            }
        }

        //public bool CommitToDatabase()
        //{
        //    try
        //    {
        //        db.SubmitChanges();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {                
        //        ErrorHandler.ErrorMessage = ex.Message;
        //        return false;
        //    }
        //}
    }
}
